package com.szholly.data.jdbc.mysql;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import com.szholly.utils.util.StringUtils;
import com.szholly.data.common.delegate.DataReaderDelegate;
import com.szholly.data.general.*;
import com.szholly.data.general.configuration.DefulatConfig;
import com.szholly.data.jdbc.DatabaseWorkspaceBase;

@Component("Workspace_DB_MySql")
@Scope("prototype")
public class MySqlWorkspaceClass extends DatabaseWorkspaceBase {

	@Autowired
	private DefulatConfig defulatConfig;

	public MySqlWorkspaceClass(ConnectionInfo info) {
		super(info);
	}

	@Override
	protected BasicDataSource getDataSource(ConnectionInfo info) {
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource
				.setUrl("jdbc:mysql://"
						+ info.getServer()
						+ ":"
						+ info.getPort()
						+ "/"
						+ info.getDatabase()
						+ "?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true");
		dataSource.setUsername(info.getUserID());
		dataSource.setPassword(info.getPassword());
		// 连接池启动时的初始值
		dataSource.setInitialSize(defulatConfig.getInitialSize());
		// 连接池的最大值
		dataSource.setMaxActive(defulatConfig.getMaxActive());
		// 最大空闲值
		dataSource.setMaxIdle(defulatConfig.getMaxIdle());
		// 最小空闲值
		dataSource.setMinIdle(defulatConfig.getMinIdle());
		return dataSource;
	}

	@Override
	protected String replaceSql(String sqlString) {
		if (StringUtils.IsNullOrSpace(sqlString))
			return sqlString;

		sqlString = sqlString.replace("[#COMBSTR#]", "||");
		sqlString = sqlString.replace("[", "").replace("]", "")
				.replace("@", ":").replace("#BDATE", "('")
				.replace("#EDATE", "')").replace("#EDATETIME", "')")
				.replace("#SYSDATETIME#", "sysdate");

		return sqlString;
	}

	/**
	 * 获取得到一个表的自动增值SQL
	 */
	@Override
	public String getAutoSql(ITable table, String fieldName) {
		return "0";
	}

	@Override
	public boolean fieldExist(String name, String fieldName) {
		name = name.trim().toUpperCase();
		fieldName = fieldName.trim().toUpperCase();
		String sql = "select count(COLUMN_NAME) as COUNTFIELDNAME from information_schema.COLUMNS where TABLE_NAME=@TABLE_NAME and COLUMN_NAME=@COLUMN_NAME";
		GlobalParameter[] para = { new GlobalParameter("@TABLE_NAME", name),
				new GlobalParameter("@COLUMN_NAME", fieldName) };
		Object obj = getExecuteScalar(sql, para);
		long cmdresult;
		if (obj == null)
			cmdresult = 0;
		else
			cmdresult = Long.parseLong(obj.toString());
		if (cmdresult == 0)
			return false;
		else
			return true;
	}

	@Override
	public ITableInfo getTableMetadate(String objName) {
		if (StringUtils.IsNullOrSpace(objName))
			return null;

		objName = objName.trim().toUpperCase();
		TableType tableType = getObjectType(objName);
		if (tableType == TableType.UnDefine)
			return null;

		TableInfoClass objMetadata = null;
		switch (tableType) {
		case Table:
			objMetadata = getTableMetadata(objName);
			break;
		case View:
			objMetadata = getViewMetadate(objName);
			break;
		case UnDefine:
		default:
			break;
		}
		return objMetadata;
	}

	/**
	 * 获取表的元数据
	 * 
	 * @param tableName
	 * @return
	 */
	protected TableInfoClass getTableMetadata(String tableName) {
		String sql = "select ORDINAL_POSITION as COLORDER, "
				+ " COLUMN_NAME as COLUMNNAME,"
				+ " DATA_TYPE as TYPENAME,"
				+ " case when DATA_TYPE = 'int' then NUMERIC_PRECISION else CHARACTER_MAXIMUM_LENGTH end as LENGTH,"
				+ " '' as PRECI,"
				+ " NUMERIC_SCALE as SCALE,"
				+ " '' as ISIDENTITY,"
				+ " case when COLUMN_KEY = 'PRI' then 'True' else 'False' end as ISPK,"
				+ " case when IS_NULLABLE='YES'  then 'True' else 'False' end as CISNULL, "
				+ " COLUMN_DEFAULT as DEFAULTVAL,"
				+ " COLUMN_COMMENT as DETEXT,"
				+ " 0 as NUM_DISTINCT"
				+ " from information_schema.COLUMNS where table_name= @tableName ORDER BY ORDINAL_POSITION ";

		GlobalParameter[] para = { new GlobalParameter("@tableName", tableName) };
		List<RowBase> schemaTable = null;
		schemaTable = this.getEntityList(RowBase.class, sql, para);

		IDataTypeConvert dataTypeConvert = new MySqlDataTypeConvert();
		HashMap<String, Field> fields = new HashMap<String, Field>();

		if (schemaTable != null && schemaTable.size() > 0) {
			for (RowBase row : schemaTable) {
				Field field = new Field();
				field.setFieldName((String) DbValueConvertUtils.GetRowObject(
						row, "COLUMNNAME"));
				field.setFieldID(StringUtils.objectToInt(DbValueConvertUtils
						.GetRowObject(row, "COLORDER")));
				field.setAllowNull(StringUtils.getBool(DbValueConvertUtils
						.GetRowObject(row, "CISNULL").toString()));
				field.setDataType(dataTypeConvert.Convert(row.getValue(
						"TYPENAME").toString()));
				if (field.getDataType() == GlobalDataType.NoDefault)
					field.setCanShow(false);
				else {
					if (RowBase.isSystemField(field.getFieldName()))
						field.setCanShow(false);
					else
						field.setCanShow(true);
				}

				if (null == DbValueConvertUtils.GetRowObject(row, "SCALE")) {
					field.setDecimaldigits(0);
				} else {
					field.setDecimaldigits(StringUtils
							.objectToInt(DbValueConvertUtils.GetRowObject(row,
									"SCALE")));
				}

				if (null == DbValueConvertUtils.GetRowObject(row, "DEFAULTVAL")) {
					field.setDefaultValue("");
				} else {
					field.setDefaultValue((String) DbValueConvertUtils
							.GetRowObject(row, "DEFAULTVAL"));
				}
				field.setFieldAliasName((String) DbValueConvertUtils
						.GetRowObject(row, "DETEXT"));
				if (null == DbValueConvertUtils.GetRowObject(row, "ISIDENTITY")) {
					field.setIsAuto(false);
				} else {
					field.setIsAuto(StringUtils.getBool(DbValueConvertUtils
							.GetRowObject(row, "ISIDENTITY").toString()));
				}
				field.setIsKey(StringUtils.getBool(DbValueConvertUtils
						.GetRowObject(row, "ISPK").toString()));
				if (null == DbValueConvertUtils.GetRowObject(row, "LENGTH"))
					field.setMaxLen(0);
				else {
					field.setMaxLen(StringUtils.objectToInt(DbValueConvertUtils
							.GetRowObject(row, "LENGTH")));
				}
				field.setReadOnly(field.getIsAuto());
				fields.put(field.getFieldName().toUpperCase().trim(), field);
			}
		}

		TableInfoClass tableMetadate = new TableInfoClass(fields);
		tableMetadate.setTableType(TableType.Table);
		return tableMetadate;
	}

	/**
	 * 获取视图元数据
	 * 
	 * @param viewName
	 *            视图名
	 * @return
	 */
	protected TableInfoClass getViewMetadate(String viewName) {
		String sql = "select ORDINAL_POSITION as colorder, "
				+ " COLUMN_NAME as ColumnName,"
				+ " DATA_TYPE as TypeName,"
				+ " case when DATA_TYPE = 'int' then NUMERIC_PRECISION else CHARACTER_MAXIMUM_LENGTH end as Length,"
				+ " '' as Preci,"
				+ " NUMERIC_SCALE as Scale,"
				+ " '' as IsIdentity,"
				+ " case when COLUMN_KEY = 'PRI' then 'True' else 'False' end as isPK,"
				+ " case when IS_NULLABLE='YES'  then 'True' else 'False' end as cisNull, "
				+ " COLUMN_DEFAULT as defaultVal,"
				+ " COLUMN_COMMENT as deText,"
				+ " 0 as NUM_DISTINCT"
				+ " from information_schema.COLUMNS where table_name= @viewName ORDER BY ORDINAL_POSITION ";

		GlobalParameter[] para = { new GlobalParameter("@viewName", viewName) };
		List<RowBase> schemaTable = null;
		try {
			schemaTable = this.getEntityList(RowBase.class, sql, para);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("获取" + viewName + "视图的元数据出错!");
		}

		IDataTypeConvert dataTypeConvert = new MySqlDataTypeConvert();
		HashMap<String, Field> fields = new HashMap<String, Field>();

		if (schemaTable != null && schemaTable.size() > 0) {
			for (RowBase row : schemaTable) {
				Field field = new Field();
				field.setFieldName((String) DbValueConvertUtils.GetRowObject(
						row, "ColumnName"));
				field.setFieldID((Integer) DbValueConvertUtils.GetRowObject(
						row, "colorder"));
				field.setAllowNull((Boolean) DbValueConvertUtils.GetRowObject(
						row, "cisNull"));
				field.setDataType(dataTypeConvert.Convert(row.getValue(
						"TypeName").toString()));
				if (field.getDataType() == GlobalDataType.NoDefault)
					field.setCanShow(false);
				else {
					if (RowBase.isSystemField(field.getFieldName()))
						field.setCanShow(false);
					else
						field.setCanShow(true);
				}

				field.setDecimaldigits((Integer) DbValueConvertUtils
						.GetRowObject(row, "Scale"));
				field.setDefaultValue((String) DbValueConvertUtils
						.GetRowObject(row, "defaultVal"));
				field.setFieldAliasName(field.getFieldName());
				field.setIsAuto((Boolean) DbValueConvertUtils.GetRowObject(row,
						"IsIdentity"));
				field.setIsKey((Boolean) DbValueConvertUtils.GetRowObject(row,
						"isPK"));
				field.setMaxLen(StringUtils.objectToInt(DbValueConvertUtils
						.GetRowObject(row, "length")));
				field.setReadOnly(true);
				fields.put(field.getFieldName().toUpperCase().trim(), field);
			}
		}

		TableInfoClass tableMetadate = new TableInfoClass(fields);
		tableMetadate.setTableType(TableType.View);
		return tableMetadate;
	}

	@Override
	public boolean objectExist(String objName) {
		if (StringUtils.IsNullOrSpace(objName)) {
			throw new RuntimeException("对象名称不能为空！");
		}
		objName = objName.trim().toUpperCase();
		String sql = "select count(table_name) as COUNTFIELDNAME from information_schema.TABLES where table_name = @tableName and TABLE_SCHEMA='"
				+ this.getDatabase()
				+ "' and (TABLE_TYPE='VIEW' or TABLE_TYPE='BASE TABLE')";
		GlobalParameter[] para = { new GlobalParameter("@tableName", objName) };

		Object obj = getExecuteScalar(sql, para);
		long cmdresult;
		if (null == obj) {
			sql = "SELECT count(name) as COUNTFIELDNAME FROM ALL_SOURCE where name=@tableName";
			obj = getExecuteScalar(sql, para);
			if (null == obj)
				cmdresult = 0;
			else
				cmdresult = Long.parseLong(obj.toString());
		} else
			cmdresult = Long.parseLong(obj.toString());

		if (cmdresult == 0)
			return false;
		else
			return true;
	}

	@Override
	public TableType getObjectType(String objName) {
		if (StringUtils.IsNullOrSpace(objName))
			return TableType.UnDefine;

		objName = objName.toUpperCase();
		String sql = "select TABLE_TYPE from information_schema.TABLES where table_name=@viewName and TABLE_SCHEMA='"
				+ this.getDatabase() + "'";
		GlobalParameter[] para = { new GlobalParameter("@viewName", objName) };
		Object obj = getExecuteScalar(sql, para);

		if (obj == null) {
			return TableType.UnDefine;
		} else {
			String typeStr = obj.toString().trim().toUpperCase();
			if (typeStr.equals("VIEW")) {
				return TableType.View;
			} else if (typeStr.equals("BASE TABLE")) {
				return TableType.Table;
			}
		}
		return TableType.UnDefine;
	}

	@SuppressWarnings("unchecked")
	@Override
	public String[] getObjects(TableType tableType) {
		String sql = "";
		switch (tableType) {
		case Table:
			sql = "select TABLE_NAME from information_schema.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA='"
					+ this.getDatabase() + "'";
			break;
		case View:
			sql = "select TABLE_NAME from information_schema.VIEWS where TABLE_TYPE='VIEW'  and TABLE_SCHEMA='"
					+ this.getDatabase() + "'";
			break;
		case UnDefine:
		default:
			break;
		}

		if (StringUtils.IsNullOrSpace(sql))
			return null;

		List<String> tabNames = new ArrayList<String>();
		DataReaderDelegate iDataReaderDelegate = new getObjectsDelegate();
		getDataReader(RowBase.class, sql, GlobalParameter.Empty,
				iDataReaderDelegate);
		tabNames = (List<String>) iDataReaderDelegate.paraObject;
		return tabNames.toArray(new String[tabNames.size()]);
	}

	public class getObjectsDelegate extends DataReaderDelegate {

		@Override
		public void invokeDataReader(IDataReader reader, Object tag) {
			List<String> tabNames = new ArrayList<String>();
			while (reader.read()) {
				tabNames.add((String) reader.getRow().getValue(
						GlobalDataType.String, "TABLE_NAME"));
			}
			this.paraObject = tabNames;
		}

	}

	@Override
	public void deleteObject(TableType tableType, String objName) {
		String sql = "";

		switch (tableType) {
		case Table:
			sql = "drop table if exists " + objName;
			break;
		case View:
			tableType = getObjectType(objName);
			if (tableType == TableType.Table)
				sql = "drop materialized view if exists " + objName;
			else if (tableType == TableType.View)
				sql = "drop view if exists" + objName;
			break;
		case UnDefine:
		default:
			break;
		}

		if (!StringUtils.IsNullOrSpace(sql))
			executeSql(sql, GlobalParameter.Empty);
	}

	@Override
	public void rename(String oldTableName, String newTableName) {
		TableType tableType = getObjectType(oldTableName);
		if (tableType == TableType.Table) {
			String sql = "RENAME TABLE " + oldTableName + " to " + newTableName;
			executeSql(sql, GlobalParameter.Empty);
		}
	}

	@Override
	public void createTable(ITableInfo tableInfo, String tableName) {
		if (StringUtils.IsNullOrSpace(tableName))
			return;

		if (!objectExist(tableName)) {
			String sql = getMetadataSql(tableInfo, tableName);
			if (StringUtils.IsNullOrSpace(sql))
				return;
			executeSql(sql, GlobalParameter.Empty);
		}
	}

	/**
	 * 如果MetadataSql.CreateSql为空时， 根据其它信息，自动填充此属性
	 * 
	 * @param tableMetadata
	 * @param tableName
	 * @return
	 */
	protected String getMetadataSql(ITableInfo tableMetadata, String tableName) {
		if (tableMetadata.getTableType() == TableType.Table) {
			IDataTypeConvert dataTypeConvert = new MySqlDataTypeConvert();

			StringBuilder sqlBuilder = new StringBuilder();
			sqlBuilder.append("Create Table " + tableName + "(");
			Field[] allFields = tableMetadata.getFields();
			for (Field field : allFields) {
				sqlBuilder.append(field.getFieldName() + " ");
				if (field.getDataType() == GlobalDataType.String
						&& field.getMaxLen() > 0) {
					sqlBuilder.append(dataTypeConvert.Convert(
							field.getDataType()).replace("254",
							String.valueOf(field.getMaxLen())));
				} else if ((field.getDataType() == GlobalDataType.Int16
						|| field.getDataType() == GlobalDataType.Int32 || field
						.getDataType() == GlobalDataType.Int64)
						&& field.getMaxLen() > 0) {
					sqlBuilder.append(dataTypeConvert.Convert(field
							.getDataType()) + "(" + field.getMaxLen() + ")");
				} else {
					sqlBuilder.append(dataTypeConvert.Convert(field
							.getDataType()));
				}

				if (field.getIsAuto()) {
					sqlBuilder.append(" AUTO_INCREMENT ,");
				} else {
					sqlBuilder.append(" ,");
				}
			}

			// 添加选择数据库
			String sql = sqlBuilder.toString();

			Field[] autoFields = tableMetadata.getAutoFields();
			// 自动增值一定为主键
			if (autoFields != null && autoFields.length > 0) {
				String keySql = " PRIMARY KEY (" + autoFields[0].getFieldName()
						+ ") ";
				sql += keySql + ")";
			} else {
				// 主键
				Field[] keyFields = tableMetadata.getKeyNoAutoFields();
				if (keyFields.length > 0) {
					String keySql = " PRIMARY KEY (";
					for (Field field : keyFields) {
						keySql += field.getFieldName() + ",";
					}
					keySql = keySql.substring(0, keySql.length() - 1) + ")";
					sql += keySql + ")";
				} else {
					sql = sql.substring(0, (sql.length() - 1)) + ")";
				}
			}
			return sql;
		}
		return "";
	}

	@Override
	public void updateTable(ITableInfo tableInfo, String tableName) {
		if (StringUtils.IsNullOrSpace(tableName))
			return;

		TableType tableType = getObjectType(tableName);
		if (tableType == TableType.Table) {
			IDataTypeConvert dataTypeConvert = new MySqlDataTypeConvert();
			List<String> sqlList = new ArrayList<String>();
			for (Field field : tableInfo.getFields()) {
				String sql = "";
				String stringFieldType = "";

				if (field.getDataType() == GlobalDataType.String)
					stringFieldType = dataTypeConvert.Convert(
							field.getDataType()).replace("254",
							String.valueOf(field.getMaxLen()));
				else
					stringFieldType = dataTypeConvert.Convert(field
							.getDataType());

				if (fieldExist(tableName, field.getFieldName()))
					sql = "ALTER TABLE " + tableName + " alter column "
							+ field.getFieldName() + " " + stringFieldType;
				else
					sql = "ALTER TABLE " + tableName + " add column "
							+ field.getFieldName() + " " + stringFieldType;

				sqlList.add(sql);
			}
			for (String item : sqlList) {
				executeSql(item, GlobalParameter.Empty);
			}
		}
	}

	@Override
	public void createOrUpdateView(String viewName, String selectSql,
			boolean isStatic) {
		viewName = viewName.trim().toUpperCase();
		deleteObject(TableType.View, viewName);
		String sql = "";
		if (isStatic)
			sql = "CREATE materialized view "
					+ viewName
					+ " build immediate refresh force on demand START WITH SYSDATE NEXT SYSDATE + 1/12 as "
					+ selectSql;
		else
			sql = "CREATE View " + viewName + " as " + selectSql;
		executeSql(sql, GlobalParameter.Empty);
	}
}